package com.cqut.servlet;

import com.alibaba.fastjson.JSON;
import com.cqut.entity.achievement;
import com.cqut.entity.course;
import com.cqut.entity.student;
import com.cqut.utils.DBUtils;
import com.cqut.utils.ResultData;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class StudentServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setContentType("text/html;charset=utf-8");
        req.setCharacterEncoding("utf-8");

        String action = req.getParameter("action") == null ? "" : req.getParameter("action");
        switch (action) {
            case"getdglist":
                getDglist(req,resp);
                break;
            case"getdglistcourse":
                getDglistCourse(req,resp);
                break;
            case"search":
                getSearch(req,resp);
                break;
            case"selectelective":
                setElective(req,resp);
                break;
            case"getme":
                getMe(req,resp);
                break;

            default:
                break;
        }
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }

    //分页
    private void getDglist(HttpServletRequest req, HttpServletResponse resp) {

        try {
            //获取参数
            String pageIndex=req.getParameter("page")==null? "1":req.getParameter("page");
            String pageSize =req.getParameter("rows")==null? "10":req.getParameter("rows");

            //获取当前学生学号
            String sno =req.getParameter("key1");

            // 将字符串转换为整数，并处理可能的异常
            int currentPage = Integer.parseInt(pageIndex);
            int pageSizeInt = Integer.parseInt(pageSize);

            // 确保页数和每页大小都是合理的值
            if (currentPage < 1) currentPage = 1;
            if (pageSizeInt < 1) pageSizeInt = 10;


            PrintWriter out = resp.getWriter();
            HashMap<String, Object> map = new HashMap<>();
            // 获取总记录数
            String sqlcount = "select count(*) from achievement";
            int total=Integer.parseInt(DBUtils.QueryScalar(sqlcount).toString());
            map.put("total", total);

            // 计算LIMIT子句的参数
            int offset = (currentPage - 1) * pageSizeInt;
            String sqllist = "SELECT * FROM ( " +
                    "  SELECT (@row_number:=@row_number + 1) AS rownumber, tmp_table.* " +
                    "  FROM ( SELECT * FROM achievement  where sno = '"+ sno+ "' ORDER BY sno ASC ) AS tmp_table, (SELECT @row_number:=0) AS t " +
                    ") AS tmp_table " +
                    "WHERE rownumber > " + offset + " " +
                    "LIMIT " + pageSize;

            //问题在这一句
            List<achievement> list = DBUtils.QueryBeanList(sqllist, achievement.class);

            map.put("rows", list);
            out.write(JSON.toJSONString(map));
        }
        catch (Exception e){
            e.printStackTrace();
        }

    }


    private void getMe(HttpServletRequest req, HttpServletResponse resp) {

        try {
            //获取参数
            String pageIndex=req.getParameter("page")==null? "1":req.getParameter("page");
            String pageSize =req.getParameter("rows")==null? "10":req.getParameter("rows");

            //获取当前学生学号
            String sno =req.getParameter("key1");

            // 将字符串转换为整数，并处理可能的异常
            int currentPage = Integer.parseInt(pageIndex);
            int pageSizeInt = Integer.parseInt(pageSize);

            // 确保页数和每页大小都是合理的值
            if (currentPage < 1) currentPage = 1;
            if (pageSizeInt < 1) pageSizeInt = 10;


            PrintWriter out = resp.getWriter();
            HashMap<String, Object> map = new HashMap<>();
            // 获取总记录数
            String sqlcount = "select count(*) from achievement";
            int total=Integer.parseInt(DBUtils.QueryScalar(sqlcount).toString());
            map.put("total", total);

            // 计算LIMIT子句的参数
            int offset = (currentPage - 1) * pageSizeInt;
            String sqllist = "SELECT * FROM ( " +
                    "  SELECT (@row_number:=@row_number + 1) AS rownumber, tmp_table.* " +
                    "  FROM ( SELECT * FROM student  where sno = '"+ sno+ "' ORDER BY sno ASC ) AS tmp_table, (SELECT @row_number:=0) AS t " +
                    ") AS tmp_table " +
                    "WHERE rownumber > " + offset + " " +
                    "LIMIT " + pageSize;

            //问题在这一句
            List<student> list = DBUtils.QueryBeanList(sqllist, student.class);

            map.put("rows", list);
            out.write(JSON.toJSONString(map));
        }
        catch (Exception e){
            e.printStackTrace();
        }

    }


    //课程查看分页
    private void getDglistCourse(HttpServletRequest req, HttpServletResponse resp) {

        try {
            //获取参数
            String pageIndex=req.getParameter("page")==null? "1":req.getParameter("page");
            String pageSize =req.getParameter("rows")==null? "10":req.getParameter("rows");

            //获取当前学生学号
            String sno =req.getParameter("key1");

            // 将字符串转换为整数，并处理可能的异常
            int currentPage = Integer.parseInt(pageIndex);
            int pageSizeInt = Integer.parseInt(pageSize);

            // 确保页数和每页大小都是合理的值
            if (currentPage < 1) currentPage = 1;
            if (pageSizeInt < 1) pageSizeInt = 10;


            PrintWriter out = resp.getWriter();
            HashMap<String, Object> map = new HashMap<>();
            // 获取总记录数
            String sqlcount = "select count(*) from course";
            int total=Integer.parseInt(DBUtils.QueryScalar(sqlcount).toString());
            map.put("total", total);

            // 计算LIMIT子句的参数
            int offset = (currentPage - 1) * pageSizeInt;
//            String sqllist = "SELECT * FROM ( " +
//                    "  SELECT (@row_number:=@row_number + 1) AS rownumber, tmp_table.* " +
//                    "  FROM ( SELECT * FROM course  where sno = '"+ sno+ "' ORDER BY sno ASC ) AS tmp_table, (SELECT @row_number:=0) AS t " +
//                    ") AS tmp_table " +
//                    "WHERE rownumber > " + offset + " " +
//                    "LIMIT " + pageSize;
            String sqllist = "SELECT c.cno, c.cname,c.teacher,c.semester,c.semester,c.week,c.cday,c.cstime,c.cetime,c.caddress from student_course s JOIN course c ON s.cno = c.cno WHERE s.sno = '"+sno+"'";

            //问题在这一句
            List<course> list = DBUtils.QueryBeanList(sqllist, course.class);

            map.put("rows", list);
            out.write(JSON.toJSONString(map));
        }
        catch (Exception e){
            e.printStackTrace();
        }

    }


    //搜索功能
    private void getSearch(HttpServletRequest req, HttpServletResponse resp) {

        try {

            PrintWriter out = resp.getWriter();
            String sno = req.getParameter("key1");

            HashMap<String, Object> map = new HashMap<>();

            int total=10;
            map.put("total", total);

            String sql = "select * from achievement where cno = ?";
            achievement achievement = DBUtils.QueryBean(sql, achievement.class, sno);

            if (achievement == null){
                out.write("404");
            }else {
                //rows属性的值似乎是一个对象，而不是一个对象数组。DataGrid通常期望rows是一个数组，每个数组元素代表一行数据。
                //需要搜索是数组，所以用list
                List<achievement> achievements = new ArrayList<>();
                if (achievement != null) {
                    achievements.add(achievement);
                }

                map.put("rows", achievements);
                String jsonString = JSON.toJSONString(map);

                out.write(jsonString);
            }
        }
        catch (Exception e){
            e.printStackTrace();
        }

    }


    //选课功能
    private void setElective(HttpServletRequest req, HttpServletResponse resp) {

        try {

            PrintWriter out = resp.getWriter();
            String cno = req.getParameter("cno");

            HashMap<String, Object> map = new HashMap<>();

            int total=10;
            map.put("total", total);

            String sql = "UPDATE elective set number = number+1 WHERE cno = ?";

            //更新成功
            if(DBUtils.Update(sql,cno) > 0){
                out.write(JSON.toJSONString(new ResultData(String.valueOf(resp.getStatus()),"保存成功")));
            }else{
                out.write(JSON.toJSONString(new ResultData("501","保存失败")));
            }
        }
        catch (Exception e){
            e.printStackTrace();
        }

    }


}
